package com.vnp.vchat.database;

import java.util.ArrayList;

import com.vnp.vchat.bean.ObjContact;
import com.vnp.vchat.bean.ObjKhachhang;
import com.vnp.vchat.bean.ObjMsgChat;
import com.vnp.vchat.util.Common;
import com.vnp.vchat.util.PreferenceHelper;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class MySQLiteHelper extends SQLiteOpenHelper{
	
	private static final String TAG = MySQLiteHelper.class.getSimpleName();
//	public static final String DATABASE_NAME = Environment.getExternalStorageDirectory()+"/vChat.db";
	public static final String DATABASE_NAME = "vChat.db";
	public static final int DATABASE_VERSION = 41;
	
	PreferenceHelper preferenceHelper;
	

	
	// tao cac truong
	public static final String TABLE_MESSAGE = "messages";
	
	public static final String ID = "_id";
	public static final String MSG_RELATION = "keyrow_id";
	public static final String MSG_ID = "msg_id";
	public static final String MSG_SEND_ID = "send_id";
	public static final String MSG_TIME = "time";
	public static final String MSG_TYPE = "type";
	public static final String MSG_NAME = "name";
	public static final String MSG_CONTENT = "content";
	public static final String MSG_URL_IMAGE = "url_image";
	public static final String MSG_AVATAR_KH = "avatar_kh";
	public static final String MSG_STATUS = "msg_status";
	
	// table khach hang
	public static final String TABLE_KH = "khach_hang";
	public static final String KH_ID = "kh_id";
	public static final String KH_KEY = "kh_key";
	public static final String KH_AVATAR = "kh_avatar";
	public static final String KH_NAME = "kh_name";
	public static final String KH_CITY = "kh_city";
	public static final String KH_PNAME = "kh_p_name";
	public static final String KH_UID = "kh_uid";
	public static final String KH_WEBSITE = "kh_website";
	public static final String KH_URL_DANGXEM = "kh_url_dangxem";
	public static final String KH_IS_ONLINE = "kh_is_online";
	
	// table history chat
	public static final String TABLE_HISCHAT = "his_chat";
	public static final String HS_ID = "his_id";
	public static final String HS_UID = "his_uid";
	public static final String HS_AVATAR = "his_avatar";
	public static final String HS_LOGIN_NAME = "his_login_name";
	public static final String HS_NAME = "his_name";
	public static final String HS_ISUSERVG = "his_is_user_vg";
	public static final String HS_LMG_CONTENT = "his_lmg_content";
	public static final String HS_LMG_TIME = "his_lmg_time";
	public static final String HS_LMG_OWER_ID = "his_lmg_owner_id";
	public static final String HS_LMG_IS_READ = "his_lmg_is_read";
	public static final String HS_URL_DANGXEM = "his_url_dang_xem";
	public static final String HS_SP_DANGXEM = "his_sp_dang_xem";
	public static final String HS_IS_ONLINE = "his_is_online";
	
	// table danh ba
	public static final String TABLE_CONTACT_PHONE = "table_contact_phone";
	public static final String CT_ID = "id";
	public static final String CT_PHONE_ID = "id_phone";
	public static final String CT_PHONE = "ct_user_name";
	public static final String CT_EMAIL = "ct_email_name";
	public static final String CT_AVATAR = "ct_avatar";
	public static final String CT_NAME = "ct_name";
	
	
	// table danh ba
	public static final String TABLE_CONTACT_PHONE_SV = "table_contact_phone_sv";
	public static final String CT_ID_SV = "id_sv";
	public static final String CT_PHONE_ID_SV = "id_phone_sv";
	public static final String CT_PHONE_SV = "ct_user_name_sv";
	public static final String CT_EMAIL_SV = "ct_email_name_sv";
	public static final String CT_AVATAR_SV = "ct_avatar_sv";
	public static final String CT_NAME_SV = "ct_name_sv";
	public static final String CT_URL_SV = "ct_url_sv";
	
	// tao bang message
	String CREATE_MSG_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_MESSAGE + "("+ ID+ " INTEGER PRIMARY KEY AUTOINCREMENT, "+MSG_RELATION+ " TEXT, " + MSG_ID + " TEXT, "
             + MSG_SEND_ID + " INTEGER, " + MSG_TIME + " INTEGER, " + MSG_TYPE + " TEXT, "+ MSG_NAME+ " TEXT, "+MSG_CONTENT+ " TEXT, "+MSG_URL_IMAGE+ " TEXT, "+MSG_AVATAR_KH+ " TEXT, "+ MSG_STATUS + " INTEGER" +")";
	
	// tao bang khach hang
	String CREATE_KH_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_KH + "("+ KH_ID+ " INTEGER PRIMARY KEY AUTOINCREMENT, "+ KH_KEY +" TEXT, " + KH_AVATAR+ " TEXT, " + KH_NAME + " TEXT, "
            + KH_CITY + " TEXT, " + KH_PNAME + " TEXT, "+ KH_WEBSITE + " TEXT, "+ KH_URL_DANGXEM + " TEXT, " + KH_IS_ONLINE + " INTEGER, " + KH_UID + " TEXT" +")";
	
	
	// tao bang history nhung nguoi da chat
	String CREATE_HIS_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_HISCHAT + "(" + HS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + HS_UID + " TEXT, "+ HS_AVATAR + " TEXT, "+ HS_LOGIN_NAME + " TEXT, " + HS_NAME + " TEXT, "
								+ HS_ISUSERVG + " INTEGER, " + HS_LMG_CONTENT + " TEXT, " + HS_LMG_TIME + " TEXT, " + HS_LMG_OWER_ID + " TEXT, " +HS_LMG_IS_READ + " INTEGER, "+ HS_URL_DANGXEM + " TEXT, "+ HS_SP_DANGXEM + " TEXT, " + HS_IS_ONLINE + " INTEGER" +")";
	
	// tao bang danh ba
	String CREATE_CONTACT_PHONE_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_CONTACT_PHONE + "("+ CT_ID+ " INTEGER PRIMARY KEY AUTOINCREMENT, "+ CT_PHONE_ID +" TEXT, " + CT_PHONE+ " TEXT, " + CT_EMAIL + " TEXT, "
             + CT_AVATAR + " TEXT, "+ CT_NAME + " TEXT" +")";
	
	// tao bang danh ba
	String CREATE_CONTACT_PHONE_TABLE_SV = "CREATE TABLE IF NOT EXISTS " + TABLE_CONTACT_PHONE_SV + "("+ CT_ID_SV+ " INTEGER PRIMARY KEY AUTOINCREMENT, "+ CT_PHONE_ID_SV +" TEXT, " + CT_PHONE_SV+ " TEXT, " + CT_EMAIL_SV + " TEXT, "
	             + CT_AVATAR_SV + " TEXT, "+ CT_NAME_SV + " TEXT" +")";
	
	public MySQLiteHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}


	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_MSG_TABLE);
		db.execSQL(CREATE_KH_TABLE);
		db.execSQL(CREATE_HIS_TABLE);
		db.execSQL(CREATE_CONTACT_PHONE_TABLE);
		db.execSQL(CREATE_CONTACT_PHONE_TABLE_SV);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_MESSAGE);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_KH);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_HISCHAT);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACT_PHONE);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACT_PHONE_SV);
		onCreate(db);
	}
	
	
	// insert phan tu vao chat;
	public void insertMSG(ObjMsgChat objMsgChat, String relation, int status, String msgId){
		try {
			SQLiteDatabase db = this.getWritableDatabase();
			ContentValues value = new ContentValues();
			
			value.put(MSG_RELATION, relation);
//			value.put(MSG_ID, objMsgChat.getMsgID());
			value.put(MSG_ID, msgId);
			value.put(MSG_SEND_ID, Integer.valueOf(objMsgChat.getFromId()));
			value.put(MSG_TIME, 0);
			value.put(MSG_TYPE, objMsgChat.getType());
			value.put(MSG_NAME, objMsgChat.getTenKhachHang());
			value.put(MSG_CONTENT, objMsgChat.getContent());
			value.put(MSG_URL_IMAGE, objMsgChat.getUrlImageKH());
			value.put(MSG_AVATAR_KH, objMsgChat.getUrlAvatarKhachhang());
			value.put(MSG_STATUS, status);
			db.insert(TABLE_MESSAGE, null, value);
			db.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
	public void updateStatusMsg(String msgId, int status){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(MSG_STATUS, status);
		
		// update   
		db.update(TABLE_MESSAGE, values, MSG_ID + " = ?", new String[]{msgId});
		
		db.close();
	}
	public void updateStatusMySMS(String msgId, int status, long vcg){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(MSG_STATUS, status);
		
		// update 
		db.update(TABLE_MESSAGE, values, MSG_ID + " = ?", new String[]{msgId});
		
		db.close();
	}
	
	public void updateMsg(String url, String key_row){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(MSG_URL_IMAGE, url);
		
		// update 
		db.update(TABLE_MESSAGE, values, MSG_SEND_ID + " = ?", new String[]{key_row});
		
		db.close();
	}
	
	
	public void deteleAllRecord(String key_row){
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_MESSAGE, MSG_RELATION + " = "+ key_row, null);
		db.close();
	}
	
	public void deleteOneHisChat(String id){
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_HISCHAT, HS_UID + " = "+ id, null);
		db.close();
	}
	
	public ArrayList<String> getIdHis(){
		SQLiteDatabase db = this.getWritableDatabase();
		ArrayList<String> items = new ArrayList<String>();
		String query = "SELECT "+HS_UID+ " FROM "+ TABLE_HISCHAT;
		
		Log.i("vChat", "SQL: "+query);
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()){
			do {
				items.add(cursor.getString(cursor.getColumnIndex(HS_UID)));
			} while (cursor.moveToNext());
			
		}
		cursor.close();
		db.close();
		return items;
	}
	
	
	// get all phan tu
	public ArrayList<ObjMsgChat> getAllMsgHis(String relation){
		ArrayList<ObjMsgChat> objMsgChats = new ArrayList<ObjMsgChat>();
		String selectQuery = "SELECT * FROM " + TABLE_MESSAGE+ " WHERE "+MSG_RELATION+ "= "+ relation ;
//		String selectQuery = "SELECT * FROM " + TABLE_MESSAGE ;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.rawQuery(selectQuery, null);
		// duyet tat ca ca hang de add vao list
				if (c.moveToFirst()) {
					do {
						ObjMsgChat objMsgChat = new ObjMsgChat();
						objMsgChat.setMsgID(c.getString(c.getColumnIndex(MSG_ID)));
						objMsgChat.setFromId(String.valueOf(c.getInt(c.getColumnIndex(MSG_SEND_ID))));
						objMsgChat.setTime(c.getInt(c.getColumnIndex(MSG_TIME)));
						objMsgChat.setType(c.getString(c.getColumnIndex(MSG_TYPE)));
						objMsgChat.setTenKhachHang(c.getString(c.getColumnIndex(MSG_NAME)));
						objMsgChat.setContent(c.getString(c.getColumnIndex(MSG_CONTENT)));
						objMsgChat.setUrlImageKH(c.getString(c.getColumnIndex(MSG_URL_IMAGE)));
						objMsgChat.setUrlAvatarKhachhang(c.getString(c.getColumnIndex(MSG_AVATAR_KH)));
						objMsgChat.setStatus(c.getInt(c.getColumnIndex(MSG_STATUS)));
						// add vao list
						objMsgChats.add(objMsgChat);
					} while (c.moveToNext());
				}
				c.close();
				if (db != null){
					db.close();	
				}
		return objMsgChats;
	}
	
	public ArrayList<ObjMsgChat> getAllMsgHisSort(String relation){
		ArrayList<ObjMsgChat> objMsgChats = new ArrayList<ObjMsgChat>();
		String selectQuery = "SELECT * FROM " + TABLE_MESSAGE+ " WHERE "+MSG_RELATION+ "= "+ relation + " ORDER BY "+ MSG_STATUS + " ASC";
//		String selectQuery = "SELECT * FROM " + TABLE_MESSAGE ;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.rawQuery(selectQuery, null);
		// duyet tat ca ca hang de add vao list
				if (c.moveToFirst()) {
					do {
						ObjMsgChat objMsgChat = new ObjMsgChat();
						objMsgChat.setMsgID(c.getString(c.getColumnIndex(MSG_ID)));
						objMsgChat.setFromId(String.valueOf(c.getInt(c.getColumnIndex(MSG_SEND_ID))));
						objMsgChat.setTime(c.getInt(c.getColumnIndex(MSG_TIME)));
						objMsgChat.setType(c.getString(c.getColumnIndex(MSG_TYPE)));
						objMsgChat.setTenKhachHang(c.getString(c.getColumnIndex(MSG_NAME)));
						objMsgChat.setContent(c.getString(c.getColumnIndex(MSG_CONTENT)));
						objMsgChat.setUrlImageKH(c.getString(c.getColumnIndex(MSG_URL_IMAGE)));
						objMsgChat.setUrlAvatarKhachhang(c.getString(c.getColumnIndex(MSG_AVATAR_KH)));
						objMsgChat.setStatus(c.getInt(c.getColumnIndex(MSG_STATUS)));
						// add vao list
						objMsgChats.add(objMsgChat);
					} while (c.moveToNext());
				}
				c.close();
				db.close();
		return objMsgChats;
	}
	
	// -- get last item chat --- //
	public ObjMsgChat getLastItemChat(String relation){
		ObjMsgChat objMsgChat = new ObjMsgChat();
		
		String select_query = "SELECT  * FROM " + TABLE_MESSAGE+ " WHERE "+MSG_RELATION+ "= "+ relation + " ORDER BY "+ MSG_ID + " DESC LIMIT 1";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.rawQuery(select_query, null);
		
		c.moveToLast();
		
		objMsgChat.setMsgID(c.getString(c.getColumnIndex(MSG_ID)));
		objMsgChat.setFromId(String.valueOf(c.getInt(c.getColumnIndex(MSG_SEND_ID))));
		objMsgChat.setTime(c.getInt(c.getColumnIndex(MSG_TIME)));
		objMsgChat.setType(c.getString(c.getColumnIndex(MSG_TYPE)));
		objMsgChat.setTenKhachHang(c.getString(c.getColumnIndex(MSG_NAME)));
		objMsgChat.setContent(c.getString(c.getColumnIndex(MSG_CONTENT)));
		objMsgChat.setUrlImageKH(c.getString(c.getColumnIndex(MSG_URL_IMAGE)));
		objMsgChat.setUrlAvatarKhachhang(c.getString(c.getColumnIndex(MSG_AVATAR_KH)));
		
		
		db.close();
		return objMsgChat;
	}
	
	public ObjMsgChat getMyLastItemChat(String relation, String myId){
		ObjMsgChat objMsgChat = new ObjMsgChat();
		
		String select_query = "SELECT  * FROM " + TABLE_MESSAGE+ " WHERE "+MSG_RELATION+ "= "+ relation + " ORDER BY "+ MSG_ID + " DESC LIMIT 1";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.rawQuery(select_query, null);
		
		c.moveToLast();
		
		objMsgChat.setMsgID(c.getString(c.getColumnIndex(MSG_ID)));
		objMsgChat.setFromId(myId);
		objMsgChat.setTime(c.getInt(c.getColumnIndex(MSG_TIME)));
		objMsgChat.setType(c.getString(c.getColumnIndex(MSG_TYPE)));
		objMsgChat.setTenKhachHang(c.getString(c.getColumnIndex(MSG_NAME)));
		objMsgChat.setContent(c.getString(c.getColumnIndex(MSG_CONTENT)));
		objMsgChat.setUrlImageKH(c.getString(c.getColumnIndex(MSG_URL_IMAGE)));
		objMsgChat.setUrlAvatarKhachhang(c.getString(c.getColumnIndex(MSG_AVATAR_KH)));
		
		
		db.close();
		return objMsgChat;
	}
	
	
	public void deleteMSgError(String msgId){
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_MESSAGE, MSG_ID + " = "+ msgId, null);
		db.close();
	}
	
	//---------------------------------------------//
	/// insert, update, delete, get all, get one phan khach hang
	
	public void insertKH(ObjKhachhang objKhachhang, String key){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		
		values.put(KH_KEY, key);
		values.put(KH_AVATAR, objKhachhang.getUrlAvatar());
		values.put(KH_NAME, objKhachhang.getName());
		values.put(KH_CITY, objKhachhang.getAddress());
		values.put(KH_PNAME, objKhachhang.getDsDaxem());
		values.put(KH_UID, objKhachhang.getId());
		values.put(KH_WEBSITE, objKhachhang.getTxtWebsite());
		values.put(KH_URL_DANGXEM, objKhachhang.getUrlDangxem());
		values.put(KH_IS_ONLINE, objKhachhang.getOnline());
		
		// insert
		db.insert(TABLE_KH, null, values);
		db.close();
	}
	
	public ArrayList<ObjKhachhang> getAllKH(String source, boolean type){
		ArrayList<ObjKhachhang> objKhachhangs = new ArrayList<ObjKhachhang>();
		
		String selectQuery = "";
		Cursor c;
		SQLiteDatabase db = this.getReadableDatabase();
		if (type == true){
			selectQuery = "SELECT * FROM " + TABLE_KH+ " ORDER BY "+KH_ID+ " DESC LIMIT 50" ;
			c = db.rawQuery(selectQuery, null);
		} else {
			selectQuery = "SELECT * FROM " + TABLE_KH+ " WHERE "+KH_WEBSITE+ "=? ORDER BY "+KH_ID+ " DESC LIMIT 50" ;
			c = db.rawQuery(selectQuery, new String[]{source});
		}
		
		// duyet tat ca ca hang de add vao list
				if (c.moveToFirst()) {
					do {
						ObjKhachhang objKhachhang = new ObjKhachhang();
						objKhachhang.setUrlAvatar(c.getString(c.getColumnIndex(KH_AVATAR)));
						objKhachhang.setName(c.getString(c.getColumnIndex(KH_NAME)));
						objKhachhang.setAddress(c.getString(c.getColumnIndex(KH_CITY)));
						objKhachhang.setDsDaxem(c.getString(c.getColumnIndex(KH_PNAME)));
						objKhachhang.setId(c.getString(c.getColumnIndex(KH_UID)));
						objKhachhang.setTxtWebsite(c.getString(c.getColumnIndex(KH_WEBSITE)));
						objKhachhang.setUrlDangxem(c.getString(c.getColumnIndex(KH_URL_DANGXEM)));
						objKhachhang.setOnline(c.getInt(c.getColumnIndex(KH_IS_ONLINE)));
						objKhachhangs.add(objKhachhang);
					} while (c.moveToNext());
				}
				c.close();
				db.close();
		return objKhachhangs;
	}
	
	public void updateKH(ObjKhachhang objKhachhang){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KH_AVATAR, objKhachhang.getUrlAvatar());
		values.put(KH_NAME, objKhachhang.getName());
		values.put(KH_CITY, objKhachhang.getAddress());
		values.put(KH_PNAME, objKhachhang.getDsDaxem());
		values.put(KH_UID, objKhachhang.getId());
		values.put(KH_WEBSITE, objKhachhang.getTxtWebsite());
		values.put(KH_URL_DANGXEM, objKhachhang.getUrlDangxem());
		values.put(KH_IS_ONLINE, objKhachhang.getOnline());
		
		// update 
		db.update(TABLE_KH, values, KH_UID + " = ?", new String[]{objKhachhang.getId()});
		
		db.close();
	}
	
	// delete kh
	public void deleteKH(String key){
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_KH, KH_UID + " = "+ key, null);
		db.close();
	}
	
	// delete all KH
	public void deleteAllKH(){
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_KH, null, null);
		db.close();
	}
	
	// get uid
	public ArrayList<String> getUID(String uid){
		SQLiteDatabase db = this.getWritableDatabase();
		ArrayList<String> items = new ArrayList<String>();
		String query = "SELECT "+KH_UID+ " FROM "+ TABLE_KH;
		
		Log.i("vChat", "SQL: "+query);
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()){
			do {
				items.add(cursor.getString(cursor.getColumnIndex(KH_UID)));
			} while (cursor.moveToNext());
			
		}
		
		cursor.close();
		db.close();
		return items;
	}
	
	public ArrayList<String> getSource(){
		ArrayList<String> lsSrc = new ArrayList<String>();
		SQLiteDatabase db = this.getReadableDatabase();
		String query = "SELECT DISTINCT "+KH_WEBSITE+ " FROM "+TABLE_KH;
		
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()){
			do {
				lsSrc.add(cursor.getString(cursor.getColumnIndex(KH_WEBSITE)));
			} while (cursor.moveToNext());
			
		}
		cursor.close();
		db.close();
		return lsSrc;
	}
	
	
	//-------------------------------------------------//
	//- thao tac phan bang his chat -//
	public void insertHisChat(ObjKhachhang objKhachhang){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(HS_UID, objKhachhang.getId());
		values.put(HS_AVATAR, objKhachhang.getUrlAvatar());
		values.put(HS_LOGIN_NAME, objKhachhang.getLoginName());
		values.put(HS_NAME, objKhachhang.getName());
		values.put(HS_ISUSERVG, objKhachhang.getIsUserVg());
		values.put(HS_LMG_CONTENT, objKhachhang.getLastMessage());
		values.put(HS_LMG_TIME, objKhachhang.getTimeLastMessage());
		values.put(HS_LMG_OWER_ID, objKhachhang.getOwnerLastMsg());
		values.put(HS_LMG_IS_READ, objKhachhang.getIsRead());
		values.put(HS_URL_DANGXEM, objKhachhang.getUrlDangxem());
		values.put(HS_SP_DANGXEM, objKhachhang.getDsDaxem());
		values.put(HS_IS_ONLINE, objKhachhang.getOnline());
		
		
		// insert
		db.insert(TABLE_HISCHAT, null, values);
		db.close();
	}
	
	
	public ArrayList<ObjKhachhang> getAllHisChat(){
		ArrayList<ObjKhachhang> objKhachhangs = new ArrayList<ObjKhachhang>();
		String selectQuery = "SELECT * FROM " + TABLE_HISCHAT+ " ORDER BY "+HS_LMG_TIME+ " DESC" ;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.rawQuery(selectQuery, null);
		// duyet tat ca ca hang de add vao list
				if (c.moveToFirst()) {
					do {
						ObjKhachhang objKhachhang = new ObjKhachhang();
						objKhachhang.setId(c.getString(c.getColumnIndex(HS_UID)));
						objKhachhang.setUrlAvatar(c.getString(c.getColumnIndex(HS_AVATAR)));
						objKhachhang.setLoginName(c.getString(c.getColumnIndex(HS_LOGIN_NAME)));
						objKhachhang.setName(c.getString(c.getColumnIndex(HS_NAME)));
						objKhachhang.setIsUserVg(c.getInt(c.getColumnIndex(HS_ISUSERVG)));
						objKhachhang.setLastMessage(c.getString(c.getColumnIndex(HS_LMG_CONTENT)));
						objKhachhang.setTimeLastMessage(c.getString(c.getColumnIndex(HS_LMG_TIME)));
						objKhachhang.setOwnerLastMsg(c.getString(c.getColumnIndex(HS_LMG_OWER_ID)));
						objKhachhang.setIsRead(c.getInt(c.getColumnIndex(HS_LMG_IS_READ)));
						objKhachhang.setUrlDangxem(c.getString(c.getColumnIndex(HS_URL_DANGXEM)));
						objKhachhang.setDsDaxem(c.getString(c.getColumnIndex(HS_SP_DANGXEM)));
						objKhachhang.setOnline(c.getInt(c.getColumnIndex(HS_IS_ONLINE)));
						objKhachhangs.add(objKhachhang);
					} while (c.moveToNext());
				}
				c.close();
				db.close();
		return objKhachhangs;
	}
	// update last msg 
	public void updateLastMsg(ObjMsgChat objMsgChat, int isRead){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(HS_UID, objMsgChat.getFromId());
		values.put(HS_LMG_CONTENT, objMsgChat.getContent());
		values.put(HS_LMG_TIME, objMsgChat.getTime());
		values.put(HS_LMG_IS_READ, isRead);
		
		// update
		db.update(TABLE_HISCHAT, values, HS_UID + " = ?", new String[]{objMsgChat.getFromId()});
		db.close();
	}
	
	/// update row da doc
	public void updateLastMsgDadoc(String row, int isRead){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(HS_LMG_IS_READ, isRead);
		
		// update
		db.update(TABLE_HISCHAT, values, HS_UID + " = ?", new String[]{row});
		db.close();
	}
	
	public int getStatusMsg(String row){
		int status = 0;
		SQLiteDatabase db = this.getReadableDatabase();
		String selectQuery = "SELECT "+HS_LMG_IS_READ+" FROM " + TABLE_HISCHAT+ " WHERE "+HS_UID + "= '"+row+"'" ;
		Cursor c = db.rawQuery(selectQuery, null);
		c.moveToNext();
		if( c != null && c.moveToFirst() ){
			status = c.getInt(c.getColumnIndex(HS_LMG_IS_READ));
			c.close();
		}
		db.close();
		return status;
	}
	
	// delete 
	public void deleteHisChat(){
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_HISCHAT, null, null);
		db.close();
	}
	
	public ArrayList<String> getAllHsUid(){
		
		SQLiteDatabase db = this.getWritableDatabase();
		ArrayList<String> items = new ArrayList<String>();
		String query = "SELECT "+HS_UID+ " FROM "+ TABLE_HISCHAT;
		
		Log.i("vChat", "SQL: "+query);
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()){
			do {
				items.add(cursor.getString(cursor.getColumnIndex(HS_UID)));
			} while (cursor.moveToNext());
			
		}
		
		cursor.close();
		db.close();
		return items;
	}
	public String getUrlAvatar(String sendId){
		String avatar = "";
		SQLiteDatabase db = this.getReadableDatabase();
		String query = "SELECT "+ HS_AVATAR + " FROM " + TABLE_HISCHAT + " WHERE "+HS_UID+ "= '"+sendId+"'";
		
		Cursor c = db.rawQuery(query, null);
		c.moveToNext();
		avatar = c.getString(c.getColumnIndex(HS_AVATAR));
		
		db.close();
		return avatar;
	}
	
	public void updateHischat(ObjKhachhang objKhachhang){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(HS_UID, objKhachhang.getId());
//		values.put(HS_AVATAR, objKhachhang.getUrlAvatar());
		values.put(HS_LOGIN_NAME, objKhachhang.getLoginName());
		values.put(HS_NAME, objKhachhang.getName());
		values.put(HS_ISUSERVG, objKhachhang.getIsUserVg());
		values.put(HS_LMG_CONTENT, objKhachhang.getLastMessage());
		values.put(HS_LMG_TIME, objKhachhang.getTimeLastMessage());
		values.put(HS_LMG_OWER_ID, objKhachhang.getOwnerLastMsg());
		values.put(HS_LMG_IS_READ, objKhachhang.getIsRead());
		values.put(HS_URL_DANGXEM, objKhachhang.getUrlDangxem());
		values.put(HS_SP_DANGXEM, objKhachhang.getDsDaxem());
		values.put(HS_IS_ONLINE, objKhachhang.getOnline());
		
		
		
		int count = db.update(TABLE_HISCHAT, values, HS_UID + " ="+objKhachhang.getId(), null);
		
		Log.i("vChat", "Update thanh cong so hang: "+count);
		
		
		if (count == 0){
			insertHisChat(objKhachhang);
		}
		db.close();
	}
	
	//---------------------thao tac voi contact-------------------//
	public void insertContact(ObjContact objContact){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(CT_PHONE_ID, objContact.getId());
		values.put(CT_PHONE, objContact.getPhone());
		values.put(CT_EMAIL, objContact.getEmail());
		values.put(CT_NAME, objContact.getName());
		
		// insert
		db.insert(TABLE_CONTACT_PHONE, null, values);
		db.close();
	}
	
	public void updateContact(ObjContact objContact, Context context){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(CT_PHONE_ID, objContact.getId());
		values.put(CT_PHONE, objContact.getPhone());
		values.put(CT_EMAIL, objContact.getEmail());
		values.put(CT_NAME, objContact.getName());
		int count = db.update(TABLE_CONTACT_PHONE, values, CT_PHONE_ID + " ="+objContact.getId(), null);
		Log.i("vChat", "Update thanh cong so hang: "+count);
		
		if (count == 0){
			insertContact(objContact);
		}
		 preferenceHelper = new PreferenceHelper(context);
		 preferenceHelper.setTimeGetContact(Common.getCurrentTime());
		 
		db.close();
	}
	
	
	public ArrayList<String> getPhoneFromDB(){
		ArrayList<String> list = new ArrayList<String>();
		
		
		SQLiteDatabase db = this.getWritableDatabase();
		String query = "SELECT "+CT_PHONE+ " FROM "+ TABLE_CONTACT_PHONE;
		
		Log.i("vChat", "SQL: "+query);
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()){
			do {
				list.add(cursor.getString(cursor.getColumnIndex(CT_PHONE)));
			} while (cursor.moveToNext());
			
		}
		
		cursor.close();
		db.close();
		return list;
	}
	
	public ArrayList<ObjContact> getContactFromDB(){
		ArrayList<ObjContact> objContacts = new ArrayList<ObjContact>();
		
		
		SQLiteDatabase db = this.getWritableDatabase();
		String query = "SELECT DISTINCT * FROM "+ TABLE_CONTACT_PHONE+ " ORDER BY "+CT_NAME+ " ASC";
		
		Log.i("vChat", "SQL: "+query);
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()){
			do {
				ObjContact objContact = new ObjContact();
				
				objContact.setPhone(cursor.getString(cursor.getColumnIndex(CT_PHONE)));
				objContact.setEmail(cursor.getString(cursor.getColumnIndex(CT_EMAIL)));
				objContact.setName(cursor.getString(cursor.getColumnIndex(CT_NAME)));
				
				objContacts.add(objContact);
			} while (cursor.moveToNext());
			
		}
		
		cursor.close();
		db.close();
		return objContacts;
	}
	
	public void updateNewMsg(ObjMsgChat objMsgChat, String relation, int status, String msgId){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		
		values.put(MSG_RELATION, relation);
//		value.put(MSG_ID, objMsgChat.getMsgID());
		values.put(MSG_ID, msgId);
		values.put(MSG_SEND_ID, Integer.valueOf(objMsgChat.getFromId()));
		values.put(MSG_TIME, 0);
		values.put(MSG_TYPE, objMsgChat.getType());
		values.put(MSG_NAME, objMsgChat.getTenKhachHang());
		values.put(MSG_CONTENT, objMsgChat.getContent());
		values.put(MSG_URL_IMAGE, objMsgChat.getUrlImageKH());
		values.put(MSG_AVATAR_KH, objMsgChat.getUrlAvatarKhachhang());
		values.put(MSG_STATUS, status);
		
		int count = db.update(TABLE_MESSAGE, values, MSG_ID + " ="+msgId, null);
		
		if (count == 0){
			db.insert(TABLE_MESSAGE, null, values);
		}
		db.close();
	}
	
}
